Você é funcionário dos correios da cidade de Boston e, como tal, supervisiona a entrega de correspondência em toda a cidade. Na maior parte, todos os pacotes enviados são eventualmente entregues. Exceto que, de vez em quando, um mistério cai no seu colo: um pacote perdido! Para cada cliente que chega até você com um relatório de pacote perdido, sua tarefa é determinar:

  1. O endereço atual (ou localização) da entrega
  2. O tipo de endereço (residencial, comercial, etc…)
  3. O conteúdo do pacote

Tudo o que você sabe é o que os próprios clientes lhe dirão. Para resolver cada mistério, você precisará usar o banco de dados do serviço de entrega de correio, packages.db BAIXAR, que contém dados sobre o trânsito de pacotes pela cidade. Usando apenas as informações do banco de dados, sua tarefa é ajudar cada cliente a encontrar o pacote perdido.

Schema

packages.db representa todas as entregas recentes de pacotes na cidade de Boston. Para fazer isso, packages.db representa as seguintes entidades:

Dentro de packages.db, você encontrará diversas tabelas. Clique nos menus abaixo para saber mais sobre o esquema de cada tabela individual.

tabela addresses (endereços)

A tabela addresses contém as seguintes colunas:

  • id: identifica o id do endereço
  • address: o endereço em si
  • type: tipo de endereço (residencial, comercial, etc…)
tabela drivers (motoristas)

A tabela drivers contém as seguintes colunas:

  • id: identifica o id do endereço
  • name: o nome do motorista
tabela packages (pacotes/encomendas)

A tabela drivers contém as seguintes colunas:

  • id: identifica o id da encomenda (pacote)
  • contents: o conteúdo da encomenda
  • from_address_id: endereço de onde o pacote foi enviado
  • to_address_id: o endereço que o pacote deve chegar. Não necessáriamente é onde ele finaliza!
tabela scans (logs)

A tabela scans contém as seguintes colunas:

  • id: identifica o id do log
  • driver_id: id do motorista que criou o log
  • package_id: endereço do pacote que foi transportado
  • address_id: id do endereço que foi feito o log
  • action: indica se o pacote foi coletado (“Picked”) ou entregue (“Drop”) no endereço
  • timestamp: indica o dia e hora que o log foi registrado

Questões para resolver

Agora que você sabe as informações do banco de dados, antes de resolver os problemas dos clientes convém determinar o diagrama ER para os dados.

1 Diagrama dos dados

Crie o diagrama ER para os dados e escreva o nome das colunas de cada tabela ao lado da mesma (retângulos no diagrama). Use algum software para criar o diagrama.

2 A carta perdida

Seu primeiro relato de um pacote desaparecido vem de Anneke. Anneke vai até o seu balcão e diz o seguinte:

Sr(a) atendente, meu nome é Anneke. Eu moro na 900 Somerville Avenue. Não faz muito tempo, enviei uma carta especial. É para meu amigo Varsha. Ela está começando um novo capítulo de sua vida em 2 Finnegan Street, na parte alta da cidade. (Esse endereço, deixe-me dizer: foi um pouco complicado acertar na primeira vez.) A carta é uma nota de felicitações – um alegre abraço de papel meu para ela, para comemorar essa grande mudança dela. Você pode verificar se já chegou até ela?

Escreva as queries usadas para responder a pergunta. Tente usar o menor número de queries possíveis para cada resposta.

3 A entrega “melindrosa”

Seu segundo relato de um pacote desaparecido vem de um sujeito misterioso de fora da cidade. Ele vai até o seu balcão e dizem o seguinte:

Bom dia para você, entregador da correspondência. Você deve se lembrar que não faz muito tempo vim da cidade de Fiftyville. Entreguei uma certa caixa em suas mãos confiáveis e pedi que mantivesse as coisas em segredo. Meu associado já esperava o pacote há algum tempo. E ainda assim, parece ter criado asas e voado para longe. Ha! Alguma chance de você ajudar a esclarecer esse mistério? Receio que não haja endereço “De”. É o tipo de pacote que poderia causar….problemas a algumas pessoas, se é que me entende!

Escreva as queries usadas para responder a pergunta. Tente usar o menor número de queries possíveis para cada resposta.

4 O presente esquecido

Seu terceiro relato de desaparecimento de um pacote vem de um avô que mora na mesma rua do correio. Ele se aproxima do seu balcão e diz o seguinte:

Ah, com licença, escriturário. Eu tinha enviado um presente misterioso para minha neta maravilhosa, no endereço 728 de Maple Place. Isso foi há cerca de duas semanas. Agora o prazo do envio já passou de sete dias inteiros e ouvi dizer que ela ainda espera, com as mãos vazias e o coração cheio de expectativas. Estou um pouco preocupado em saber para onde foi meu pacote. Não consigo lembrar o que há dentro, mas sei que está cheio até a borda com meu amor por ela. Podemos rastreá-lo para que possa encher o dia dela de alegria? Enviei-o da minha casa, no enderço 109 da Tileston Street.

Escreva as queries usadas para responder a pergunta. Tente usar o menor número de queries possíveis para cada resposta.